package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.ChildrenBehaviorPurpose;
import orm.CollectivityHomeworkInfo;
import orm.CollectivityWeekTimeBucket;
import util.DBHelper;

public class CollectivityHomeworkInfoDAO {
	
	
	public void insertCHomeworkInfo(Statement statement, 
			ArrayList<CollectivityHomeworkInfo> chwiArr, String tpID) throws SQLException {
		CollectivityHomeworkInfo chwi = null;
		for (int i = 0; i < chwiArr.size(); i++) {
			chwi = chwiArr.get(i);
			statement.execute("insert into collectivityhomeworkinfo values('" + tpID + "'," +
					chwi.getCHomeworkID() + ", '" + chwi.getTaskDefinition() + "', '" + chwi.getMaterial() + 
					"','" + chwi.getGuidingMeans() + "'," + chwi.getCompleteEntering() + ",'" + chwi.getRemark() + "')");
		}
	}
	
	public void updateCHomeworkInfo(Statement statement,
			ArrayList<CollectivityHomeworkInfo> chwiArr, String tpID) throws SQLException {
		CollectivityHomeworkInfo chwi = null;
		for (int i = 0; i < chwiArr.size(); i++) {
			chwi = chwiArr.get(i);

			StringBuffer sqlStr = new StringBuffer();
			sqlStr.append("update collectivityhomeworkinfo set TaskDefinition = '" );
			sqlStr.append(chwi.getTaskDefinition() + "', Material ='" + chwi.getMaterial() + "', GuidingMeans = '" );
			sqlStr.append(chwi.getGuidingMeans() + "', CompleteEntering =" + chwi.getCompleteEntering() + ", Remark = '" + chwi.getRemark());
			sqlStr.append("' where TPID = '" + tpID + "' and CHomeworkID = " + chwi.getCHomeworkID() + "");
			statement.execute(sqlStr.toString());
		}
	}
	
	public void deleteCHomeworkInfo(Statement statement, String tpID) throws SQLException {
		statement.execute("delete from collectivityhomeworkinfo where TPID='" + tpID + "'");
	}
	
	public ArrayList<CollectivityHomeworkInfo> getCHWIByTPID(String tpID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<CollectivityHomeworkInfo> al = new ArrayList<CollectivityHomeworkInfo>();
		CollectivityHomeworkInfo chwi = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityhomeworkinfo where TPID='" + tpID + "'");
		
		while (rs.next()) {
			chwi = new CollectivityHomeworkInfo();
			int i = 1;
			chwi.setTPID(rs.getString(i++));
			chwi.setCHomeworkID(rs.getInt(i++));
			chwi.setTaskDefinition(rs.getString(i++));
			chwi.setMaterial(rs.getString(i++));
			chwi.setGuidingMeans(rs.getString(i++));
			chwi.setCompleteEntering(rs.getInt(i++));
			chwi.setRemark(rs.getString(i++));
			al.add(chwi);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
}
